【SQL分享】如何在oracle找到前12個月連續四個月的平均消費金額

oracle sql

暐翰 2018-09-01 09:15:44 ‧ 7245 瀏覽

分享至

表格結構

月份	消費金額
01/04/17	10000
01/05/17	10000
01/06/17	10000
01/07/17	13000
01/08/17	11000
01/09/17	12000
01/10/17	10000
01/11/17	12000

期望結果

日期區間	平均消費金額
04/17 - 07/17	10750
05/17 - 08/17	11250
06/17 - 09/17	11750
07/17 - 10/17	12250
08/17 - 11/17	12750
09/17 - 12/17	13250
10/17 - 01/18	13750
11/17 - 02/18	14250
12/17 - 03/18	14750

回答

使用connect by level獲取十二個月資料
使用ADD_MONTHS將目前時間(sysdate)-(level+3)跟(level-1)取得連續四個月資料
使用group + avg 取得平均資料

with CTE as (
    select 
        --使用ADD_MONTHS-3跟-1取得連續四個月資料
        TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- (level+3)),'YYYY/MM/')||'01','yyyy/mm/dd') sDate
        ,TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- level),'YYYY/MM/')||'01','yyyy/mm/dd') eDate
        ,level 
    from dual 
    --使用`connect by level`獲取十二個月資料
    connect by level <=12 
)
,CTE2 as (
  select * 
  from CTE T1
  left join T T2 on cast(T2."Month" as date) between T1.sDate and T1.eDate
)
select TO_CHAR(sDate,'MM/YY') || ' - ' || TO_CHAR(eDate,'MM/YY') as "Consecutive Months"
  ,round(avg("Consumption")) as "Avg. of 4 Months Consumption"
from CTE2
group by sDate,eDate
order by eDate

得到結果

Consecutive Months	Avg. of 4 Months Consumption
04/17 - 07/17	10750
05/17 - 08/17	11000
06/17 - 09/17	11500
07/17 - 10/17	11500
08/17 - 11/17	11250
09/17 - 12/17	11333
10/17 - 01/18	11000
11/17 - 02/18	12000
12/17 - 03/18	(null)
01/18 - 04/18	(null)
02/18 - 05/18	(null)
03/18 - 06/18	(null)

Test DDL:

CREATE TABLE T
    ("Month" timestamp, "Consumption" int)
;

INSERT ALL 
    INTO T ("Month", "Consumption")
         VALUES ('01-Apr-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-May-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Jun-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Jul-2017 12:00:00 AM', 13000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Aug-2017 12:00:00 AM', 11000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Sep-2017 12:00:00 AM', 12000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Oct-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Nov-2017 12:00:00 AM', 12000)
SELECT * FROM dual
;

線上測試連結

假如大大們有更好做法，都可以留言討論。

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

2 則留言

一級屠豬士

iT邦新手 2 級 ‧ 2018-09-02 17:07:40

create table ithelp180902 (
  id serial primary key
, cts timestamp not null
, consumption int not null  
);

insert into ithelp180902(cts, consumption) values
('01-Apr-2017 12:00:00 AM', 10000),
('01-May-2017 12:00:00 AM', 10000),
('01-Jun-2017 12:00:00 AM', 10000),
('01-Jul-2017 12:00:00 AM', 13000),
('01-Aug-2017 12:00:00 AM', 11000),
('01-Sep-2017 12:00:00 AM', 12000),
('01-Oct-2017 12:00:00 AM', 10000),
('01-Nov-2017 12:00:00 AM', 12000);

with t1 as (
select extract(YEAR FROM cts) cyear
     , extract(MONTH FROM cts) cmonth
     , sum(consumption) sum_consum
  from ithelp180902
 group by extract(YEAR FROM cts), extract(MONTH FROM cts)
), t2 as (
select *
     , round(avg(sum_consum) over(order by cyear, cmonth rows between current row and 3 following),0) avg_consum
  from t1
), t3 as (
select extract(YEAR FROM n) cyear
     , extract(MONTH FROM n) cmonth
     , to_char(n, 'YYYY-MM')
    || ' => ' 
    || to_char((n + interval '3 months'), 'YYYY-MM') date_range
  from generate_series('2017-04-01'::timestamp, '2018-03-01'::timestamp, '1 months') g(n)
)
select t3.date_range
     , t2.avg_consum
  from t2
  join t3
 using (cyear, cmonth)
;  


     date_range     | avg_consum 
--------------------+------------
 2017-04 => 2017-07 |      10750
 2017-05 => 2017-08 |      11000
 2017-06 => 2017-09 |      11500
 2017-07 => 2017-10 |      11500
 2017-08 => 2017-11 |      11250
 2017-09 => 2017-12 |      11333
 2017-10 => 2018-01 |      11000
 2017-11 => 2018-02 |      12000
(8 筆資料列)

回應 1
檢舉

暐翰 iT邦大師 1 級 ‧ 2018-09-03 09:41:49 檢舉

驚嘆時間~

一.真方便，PGSQL可以簡單使用generate_series指定一個範圍時間，並指定格式切分成多個row
oracle還要用模擬的方式
可以參考Oracle实现POSTGRESQL的generate_series功能 - CSDN博客

select extract(YEAR FROM n) cyear
     , extract(MONTH FROM n) cmonth
     , to_char(n, 'YYYY-MM')
    || ' => ' 
    || to_char((n + interval '3 months'), 'YYYY-MM') date_range
from generate_series('2017-04-01'::timestamp, '2018-03-01'::timestamp, '1 months') g(n)

二.運用windows function : rows between current row and 3 following 簡化SQL取得連續四個月資料

select *
     , round(avg(sum_consum) over(order by cyear, cmonth rows between current row and 3 following),0) avg_consum
from t1

登入發表回應

純真的人

iT邦大師 1 級 ‧ 2018-09-03 16:40:18

恩??子查詢呢??

oracle能用子查詢嗎@@?

declare @Tab table(
	Months date
	,Consumption int
)
insert into @Tab
values('2017/4/17',10000)
,('2017/5/17',10000)
,('2017/6/17',10000)
,('2017/7/17',13000)
,('2017/8/17',11000)
,('2017/9/17',12000)
,('2017/10/17',10000)
,('2017/11/17',12000)

select Months
,(
	select avg(b.Consumption)
	from @Tab as b
	where b.Months between a.Months and dateadd(m,3,a.Months)
) as Avg_Consumption
from @Tab as a

第一次用oracle
用線上網址測試~好像可以@_@a

select "Month"
,(
	select avg("b"."Consumption")
	from T "b"
    where "b"."Month" between "a"."Month" and ADD_MONTHS("a"."Month",3)

) as Avg_Consumption
from T "a"

http://sqlfiddle.com/#!4/bba7f/42

回應 2
檢舉

暐翰 iT邦大師 1 級 ‧ 2018-09-03 18:36:07 檢舉

純真的人大大
不好意思，我描述資料不好
因為他的日期不一定剛好隔一個月
是一個明細表格，所以要使用 (2017-04 => 2017-07)格式
而不是2017/11/17

純真的人 iT邦大師 1 級 ‧ 2018-09-03 18:49:04 檢舉

可以先轉換成只有年/月/1也是可以的~
只是會多幾句轉換SQL

declare @Tab table(
	Months date
	,Consumption int
)
insert into @Tab
values('2017/4/17',10000)
,('2017/5/17',10000)
,('2017/6/18',10000)
,('2017/7/17',13000)
,('2017/8/17',11000)
,('2017/9/19',12000)
,('2017/10/25',10000)
,('2017/11/17',12000)

select Months
,(
	select avg(b.Consumption)
	from @Tab as b
	where Convert(date,Convert(varchar,year(b.Months)) + '/' + Convert(varchar,month(b.Months)) + '/1') between Convert(date,Convert(varchar,year(a.Months)) + '/' + Convert(varchar,month(a.Months)) + '/1') and dateadd(m,3,Convert(date,Convert(varchar,year(a.Months)) + '/' + Convert(varchar,month(a.Months)) + '/1'))
) as Avg_Consumption
from @Tab as a

或者2012以上

select Months
,(
	select avg(b.Consumption)
	from @Tab as b
	where DATEFROMPARTS(year(b.Months),month(b.Months),1) 
	between DATEFROMPARTS(year(a.Months),month(a.Months),1) 
	and DATEFROMPARTS(year(a.Months),month(a.Months),1)
) as Avg_Consumption
from @Tab as a

登入發表回應